
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 12/01/2010 08:46:42
-- Generated from EDMX file: C:\Users\stewarts\Documents\Visual Studio 2010\Projects\APH.Parliament2.0.Data\Branches\ParlInfoDevelopment\Source\APH.Parl2.Shared.Data\ParlInfo\ParlInfo.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [ParlInfo];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_BillsDigestBill]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[BillsDigests] DROP CONSTRAINT [FK_BillsDigestBill];
GO
IF OBJECT_ID(N'[dbo].[FK_GenericSearchResultBill]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Bills] DROP CONSTRAINT [FK_GenericSearchResultBill];
GO
IF OBJECT_ID(N'[dbo].[FK_GenericSearchResultParliamentarian]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Parliamentarians] DROP CONSTRAINT [FK_GenericSearchResultParliamentarian];
GO
IF OBJECT_ID(N'[dbo].[FK_ReportInquiry]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Reports] DROP CONSTRAINT [FK_ReportInquiry];
GO
IF OBJECT_ID(N'[dbo].[FK_InquiryCommittee]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Inquiries] DROP CONSTRAINT [FK_InquiryCommittee];
GO
IF OBJECT_ID(N'[dbo].[FK_HansardGenericSearchResult]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[HansardSections] DROP CONSTRAINT [FK_HansardGenericSearchResult];
GO
IF OBJECT_ID(N'[dbo].[FK_CommitteeGenericSearchResult]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Committees] DROP CONSTRAINT [FK_CommitteeGenericSearchResult];
GO
IF OBJECT_ID(N'[dbo].[FK_HansardInquiry]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[HansardSections] DROP CONSTRAINT [FK_HansardInquiry];
GO
IF OBJECT_ID(N'[dbo].[FK_Questioner_Hansard]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Questioner] DROP CONSTRAINT [FK_Questioner_Hansard];
GO
IF OBJECT_ID(N'[dbo].[FK_Questioner_Parliamentarian]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Questioner] DROP CONSTRAINT [FK_Questioner_Parliamentarian];
GO
IF OBJECT_ID(N'[dbo].[FK_Responder_Hansard]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Responder] DROP CONSTRAINT [FK_Responder_Hansard];
GO
IF OBJECT_ID(N'[dbo].[FK_Responder_Parliamentarian]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Responder] DROP CONSTRAINT [FK_Responder_Parliamentarian];
GO
IF OBJECT_ID(N'[dbo].[FK_Interjector_Hansard]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Interjector] DROP CONSTRAINT [FK_Interjector_Hansard];
GO
IF OBJECT_ID(N'[dbo].[FK_Interjector_Parliamentarian]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Interjector] DROP CONSTRAINT [FK_Interjector_Parliamentarian];
GO
IF OBJECT_ID(N'[dbo].[FK_Presenter_Hansard]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Presenter] DROP CONSTRAINT [FK_Presenter_Hansard];
GO
IF OBJECT_ID(N'[dbo].[FK_Presenter_Parliamentarian]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Presenter] DROP CONSTRAINT [FK_Presenter_Parliamentarian];
GO
IF OBJECT_ID(N'[dbo].[FK_Speaker_Hansard]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Speaker] DROP CONSTRAINT [FK_Speaker_Hansard];
GO
IF OBJECT_ID(N'[dbo].[FK_Speaker_Parliamentarian]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Speaker] DROP CONSTRAINT [FK_Speaker_Parliamentarian];
GO
IF OBJECT_ID(N'[dbo].[FK_NewsItemCommittee]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[NewsItems] DROP CONSTRAINT [FK_NewsItemCommittee];
GO
IF OBJECT_ID(N'[dbo].[FK_NewsItemGenericSearchResult]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[NewsItems] DROP CONSTRAINT [FK_NewsItemGenericSearchResult];
GO
IF OBJECT_ID(N'[dbo].[FK_InquiryGenericSearchResult]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Inquiries] DROP CONSTRAINT [FK_InquiryGenericSearchResult];
GO
IF OBJECT_ID(N'[dbo].[FK_GenericSearchResultPublication]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Publications] DROP CONSTRAINT [FK_GenericSearchResultPublication];
GO
IF OBJECT_ID(N'[dbo].[FK_GenericSearchResultPhoto]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Photos] DROP CONSTRAINT [FK_GenericSearchResultPhoto];
GO
IF OBJECT_ID(N'[dbo].[FK_CommitteeMembers_Committee]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CommitteeMembers] DROP CONSTRAINT [FK_CommitteeMembers_Committee];
GO
IF OBJECT_ID(N'[dbo].[FK_CommitteeMembers_Parliamentarian]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CommitteeMembers] DROP CONSTRAINT [FK_CommitteeMembers_Parliamentarian];
GO
IF OBJECT_ID(N'[dbo].[FK_ParliamentarianFirstSpeech]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[HansardSections] DROP CONSTRAINT [FK_ParliamentarianFirstSpeech];
GO
IF OBJECT_ID(N'[dbo].[FK_BillHansard]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[HansardSections] DROP CONSTRAINT [FK_BillHansard];
GO
IF OBJECT_ID(N'[dbo].[FK_BillSecondReadingSpeeches]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[HansardSections] DROP CONSTRAINT [FK_BillSecondReadingSpeeches];
GO
IF OBJECT_ID(N'[dbo].[FK_BillMinisterOrSponsorSpeech]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[HansardSections] DROP CONSTRAINT [FK_BillMinisterOrSponsorSpeech];
GO
IF OBJECT_ID(N'[dbo].[FK_BillSpeechesOnConsideration]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[HansardSections] DROP CONSTRAINT [FK_BillSpeechesOnConsideration];
GO
IF OBJECT_ID(N'[dbo].[FK_BillProposedAmendment]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ProposedAmendments] DROP CONSTRAINT [FK_BillProposedAmendment];
GO
IF OBJECT_ID(N'[dbo].[FK_BillScheduleOfAmendments]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ScheduleOfAmendments] DROP CONSTRAINT [FK_BillScheduleOfAmendments];
GO
IF OBJECT_ID(N'[dbo].[FK_BillInquiry]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Inquiries] DROP CONSTRAINT [FK_BillInquiry];
GO
IF OBJECT_ID(N'[dbo].[FK_BillCommittee_Bill]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[BillCommittee] DROP CONSTRAINT [FK_BillCommittee_Bill];
GO
IF OBJECT_ID(N'[dbo].[FK_BillCommittee_Committee]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[BillCommittee] DROP CONSTRAINT [FK_BillCommittee_Committee];
GO
IF OBJECT_ID(N'[dbo].[FK_BillExplanatoryMemorandum]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ExplanatoryMemoranda] DROP CONSTRAINT [FK_BillExplanatoryMemorandum];
GO
IF OBJECT_ID(N'[dbo].[FK_HansardSectionFullTranscript]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[HansardSections] DROP CONSTRAINT [FK_HansardSectionFullTranscript];
GO
IF OBJECT_ID(N'[dbo].[FK_ChamberDocumentFullTOC]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ChamberDocuments] DROP CONSTRAINT [FK_ChamberDocumentFullTOC];
GO
IF OBJECT_ID(N'[dbo].[FK_ChamberDocumentSearchTabledPaper]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TabledDocuments] DROP CONSTRAINT [FK_ChamberDocumentSearchTabledPaper];
GO
IF OBJECT_ID(N'[dbo].[FK_ChamberDocumentSearchChamberDocument]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ChamberDocuments] DROP CONSTRAINT [FK_ChamberDocumentSearchChamberDocument];
GO
IF OBJECT_ID(N'[dbo].[FK_SectionTranscript]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[HansardSections] DROP CONSTRAINT [FK_SectionTranscript];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Bills]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Bills];
GO
IF OBJECT_ID(N'[dbo].[BillsDigests]', 'U') IS NOT NULL
    DROP TABLE [dbo].[BillsDigests];
GO
IF OBJECT_ID(N'[dbo].[TariffProposals]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TariffProposals];
GO
IF OBJECT_ID(N'[dbo].[BillsLists]', 'U') IS NOT NULL
    DROP TABLE [dbo].[BillsLists];
GO
IF OBJECT_ID(N'[dbo].[Committees]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Committees];
GO
IF OBJECT_ID(N'[dbo].[Inquiries]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Inquiries];
GO
IF OBJECT_ID(N'[dbo].[HansardSections]', 'U') IS NOT NULL
    DROP TABLE [dbo].[HansardSections];
GO
IF OBJECT_ID(N'[dbo].[Parliamentarians]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Parliamentarians];
GO
IF OBJECT_ID(N'[dbo].[GenericSearchResults]', 'U') IS NOT NULL
    DROP TABLE [dbo].[GenericSearchResults];
GO
IF OBJECT_ID(N'[dbo].[Reports]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Reports];
GO
IF OBJECT_ID(N'[dbo].[NewsItems]', 'U') IS NOT NULL
    DROP TABLE [dbo].[NewsItems];
GO
IF OBJECT_ID(N'[dbo].[Photos]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Photos];
GO
IF OBJECT_ID(N'[dbo].[Publications]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Publications];
GO
IF OBJECT_ID(N'[dbo].[ProposedAmendments]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ProposedAmendments];
GO
IF OBJECT_ID(N'[dbo].[ScheduleOfAmendments]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ScheduleOfAmendments];
GO
IF OBJECT_ID(N'[dbo].[ExplanatoryMemoranda]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ExplanatoryMemoranda];
GO
IF OBJECT_ID(N'[dbo].[HansardFullTranscripts]', 'U') IS NOT NULL
    DROP TABLE [dbo].[HansardFullTranscripts];
GO
IF OBJECT_ID(N'[dbo].[ChamberDocuments]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ChamberDocuments];
GO
IF OBJECT_ID(N'[dbo].[ChamberDocumentTOCs]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ChamberDocumentTOCs];
GO
IF OBJECT_ID(N'[dbo].[ChamberDocumentSearchResults]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ChamberDocumentSearchResults];
GO
IF OBJECT_ID(N'[dbo].[TabledDocuments]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TabledDocuments];
GO
IF OBJECT_ID(N'[dbo].[HansardSectionTranscripts]', 'U') IS NOT NULL
    DROP TABLE [dbo].[HansardSectionTranscripts];
GO
IF OBJECT_ID(N'[dbo].[Questioner]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Questioner];
GO
IF OBJECT_ID(N'[dbo].[Responder]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Responder];
GO
IF OBJECT_ID(N'[dbo].[Interjector]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Interjector];
GO
IF OBJECT_ID(N'[dbo].[Presenter]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Presenter];
GO
IF OBJECT_ID(N'[dbo].[Speaker]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Speaker];
GO
IF OBJECT_ID(N'[dbo].[CommitteeMembers]', 'U') IS NOT NULL
    DROP TABLE [dbo].[CommitteeMembers];
GO
IF OBJECT_ID(N'[dbo].[BillCommittee]', 'U') IS NOT NULL
    DROP TABLE [dbo].[BillCommittee];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Bills'
CREATE TABLE [dbo].[Bills] (
    [Id] nvarchar(100)  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Date] datetime  NOT NULL,
    [Summary] nvarchar(max)  NOT NULL,
    [IndexedContent] nvarchar(max)  NOT NULL,
    [Number] nvarchar(max)  NOT NULL,
    [Status] nvarchar(max)  NOT NULL,
    [LongTitle] nvarchar(max)  NOT NULL,
    [FirstReadingWordLink] nvarchar(max)  NOT NULL,
    [FirstReadingHTMLLink] nvarchar(max)  NOT NULL,
    [Type] nvarchar(max)  NOT NULL,
    [Portfolio] nvarchar(max)  NOT NULL,
    [Sponsor] nvarchar(max)  NOT NULL,
    [FirstReadingPDFLink] nvarchar(max)  NOT NULL,
    [AsPassedWordLink] nvarchar(max)  NOT NULL,
    [AsPassedHTMLLink] nvarchar(max)  NOT NULL,
    [AsPassedPDFLink] nvarchar(max)  NOT NULL,
    [LastModified] datetime  NOT NULL,
    [ParlInfoBillHomeLink] nvarchar(max)  NOT NULL,
    [ProgressStages] nvarchar(max)  NOT NULL,
    [OriginatingChamber] nvarchar(max)  NOT NULL,
    [ParliamentNumber] int  NOT NULL,
    [ProposedAmendmentsList] nvarchar(max)  NOT NULL,
    [GenericSearchResultBill_Bill_Id] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'BillsDigests'
CREATE TABLE [dbo].[BillsDigests] (
    [Id] nvarchar(100)  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Date] datetime  NOT NULL,
    [Summary] nvarchar(max)  NOT NULL,
    [DocumentHTMLLink] nvarchar(max)  NOT NULL,
    [DocumentPDFLink] nvarchar(max)  NOT NULL,
    [Bill_Id] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'TariffProposals'
CREATE TABLE [dbo].[TariffProposals] (
    [Id] nvarchar(100)  NOT NULL,
    [Date] datetime  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Type] nvarchar(max)  NOT NULL,
    [Summary] nvarchar(max)  NULL,
    [Link] nvarchar(max)  NOT NULL,
    [OriginatingChamber] nvarchar(max)  NOT NULL,
    [ParliamentNumber] int  NOT NULL
);
GO

-- Creating table 'BillsLists'
CREATE TABLE [dbo].[BillsLists] (
    [Id] nvarchar(100)  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Date] datetime  NOT NULL,
    [Summary] nvarchar(max)  NOT NULL,
    [Link] nvarchar(max)  NOT NULL,
    [Chamber] nvarchar(max)  NOT NULL,
    [ParliamentNumber] int  NOT NULL,
    [Year] int  NOT NULL
);
GO

-- Creating table 'Committees'
CREATE TABLE [dbo].[Committees] (
    [Id] nvarchar(100)  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [DateAppointed] datetime  NOT NULL,
    [DateCompleted] datetime  NULL,
    [Status] nvarchar(max)  NOT NULL,
    [Type] nvarchar(max)  NOT NULL,
    [Summary] nvarchar(max)  NOT NULL,
    [IndexedContent] nvarchar(max)  NOT NULL,
    [Link] nvarchar(max)  NOT NULL,
    [ParliamentNumber] int  NOT NULL,
    [Chamber] nvarchar(max)  NOT NULL,
    [Joint] bit  NOT NULL,
    [CommitteeGenericSearchResult_Committee_Id] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'Inquiries'
CREATE TABLE [dbo].[Inquiries] (
    [Id] nvarchar(100)  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [DateInitiated] datetime  NOT NULL,
    [DateCompleted] datetime  NOT NULL,
    [Link] nvarchar(max)  NOT NULL,
    [Status] nvarchar(max)  NOT NULL,
    [LastModified] datetime  NOT NULL,
    [ParliamentNumber] int  NOT NULL,
    [Chamber] nvarchar(max)  NOT NULL,
    [Committee_Id] nvarchar(100)  NOT NULL,
    [InquiryGenericSearchResult_Inquiry_Id] nvarchar(100)  NOT NULL,
    [Bill_Id] nvarchar(100)  NULL
);
GO

-- Creating table 'HansardSections'
CREATE TABLE [dbo].[HansardSections] (
    [Id] nvarchar(100)  NOT NULL,
    [PublicationStatus] nvarchar(max)  NOT NULL,
    [Date] datetime  NOT NULL,
    [Fragment] int  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [IndexedContent] nvarchar(max)  NOT NULL,
    [TranscriptPDFLink] nvarchar(max)  NOT NULL,
    [Context] nvarchar(max)  NOT NULL,
    [Type] nvarchar(max)  NOT NULL,
    [ParliamentNumber] int  NOT NULL,
    [HansardGenericSearchResult_Hansard_Id] nvarchar(100)  NOT NULL,
    [Inquiry_Id] nvarchar(100)  NULL,
    [ParliamentarianFirstSpeech_HansardSection_Id] nvarchar(100)  NULL,
    [Bill_Id] nvarchar(100)  NOT NULL,
    [BillSecondReadingSpeeches_HansardSection_Id] nvarchar(100)  NOT NULL,
    [BillMinisterOrSponsorSpeech_HansardSection_Id] nvarchar(100)  NOT NULL,
    [BillSpeechesOnConsideration_HansardSection_Id] nvarchar(100)  NOT NULL,
    [FullTranscript_Id] nvarchar(100)  NOT NULL,
    [SectionTranscript_Id] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'Parliamentarians'
CREATE TABLE [dbo].[Parliamentarians] (
    [Id] nvarchar(100)  NOT NULL,
    [FirstName] nvarchar(max)  NOT NULL,
    [Surname] nvarchar(max)  NOT NULL,
    [Party] nvarchar(max)  NOT NULL,
    [Electorate] nvarchar(max)  NOT NULL,
    [State] nvarchar(max)  NOT NULL,
    [Current] bit  NOT NULL,
    [Salutation] nvarchar(max)  NOT NULL,
    [ImageLink] nvarchar(max)  NOT NULL,
    [ParliamentaryService] nvarchar(max)  NULL,
    [ParliamentaryPositions] nvarchar(max)  NULL,
    [MinisterialAppointments] nvarchar(max)  NULL,
    [CommitteeService] nvarchar(max)  NULL,
    [ConferencesDelegationsVisits] nvarchar(max)  NULL,
    [ParliamentaryPartyPositions] nvarchar(max)  NULL,
    [PartyPositions] nvarchar(max)  NULL,
    [Personal] nvarchar(max)  NULL,
    [PreParliament] nvarchar(max)  NULL,
    [Publications] nvarchar(max)  NULL,
    [ElectorateDescription] nvarchar(max)  NOT NULL,
    [LocalGovtService] nvarchar(max)  NULL,
    [ParliamentaryAppointments] nvarchar(max)  NULL,
    [Chamber] nvarchar(max)  NOT NULL,
    [MilitaryService] nvarchar(max)  NULL,
    [GenericSearchResultParliamentarian_Parliamentarian_Id] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'GenericSearchResults'
CREATE TABLE [dbo].[GenericSearchResults] (
    [Id] nvarchar(100)  NOT NULL,
    [Collection] nvarchar(max)  NOT NULL,
    [Type] nvarchar(max)  NOT NULL,
    [Summary] nvarchar(max)  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Link] nvarchar(max)  NOT NULL,
    [IndexedContent] nvarchar(max)  NOT NULL,
    [Date] datetime  NOT NULL
);
GO

-- Creating table 'Reports'
CREATE TABLE [dbo].[Reports] (
    [Id] nvarchar(100)  NOT NULL,
    [ReportHomeLink] nvarchar(max)  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [DateTabled] datetime  NOT NULL,
    [IndexedContent] nvarchar(max)  NOT NULL,
    [ReportPDFLink] nvarchar(max)  NOT NULL,
    [Inquiry_Id] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'NewsItems'
CREATE TABLE [dbo].[NewsItems] (
    [Id] nvarchar(100)  NOT NULL,
    [Source] nvarchar(max)  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Date] datetime  NOT NULL,
    [IndexedContent] nvarchar(max)  NOT NULL,
    [Summary] nvarchar(max)  NOT NULL,
    [Chamber] nvarchar(max)  NOT NULL,
    [Committee_Id] nvarchar(100)  NULL,
    [NewsItemGenericSearchResult_NewsItem_Id] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'Photos'
CREATE TABLE [dbo].[Photos] (
    [Id] nvarchar(50)  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Album] nvarchar(max)  NOT NULL,
    [Date] datetime  NOT NULL,
    [Summary] nvarchar(max)  NOT NULL,
    [Link] nvarchar(max)  NOT NULL,
    [ImageLink] nvarchar(max)  NOT NULL,
    [IndexedContent] nvarchar(max)  NOT NULL,
    [GenericSearchResultPhoto_Photo_Id] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'Publications'
CREATE TABLE [dbo].[Publications] (
    [Id] nvarchar(50)  NOT NULL,
    [Date] datetime  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Link] nvarchar(max)  NOT NULL,
    [PDFLink] nvarchar(max)  NOT NULL,
    [PublishingArea] nvarchar(max)  NOT NULL,
    [Summary] nvarchar(max)  NOT NULL,
    [PublicationType] nvarchar(max)  NOT NULL,
    [IndexedContent] nvarchar(max)  NOT NULL,
    [Authors] nvarchar(max)  NOT NULL,
    [GenericSearchResultPublication_Publication_Id] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'ProposedAmendments'
CREATE TABLE [dbo].[ProposedAmendments] (
    [Id] nvarchar(100)  NOT NULL,
    [Proposer] nvarchar(max)  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [DocumentWordLink] nvarchar(max)  NOT NULL,
    [DocumentPDFLink] nvarchar(max)  NOT NULL,
    [DocumentHTMLLink] nvarchar(max)  NOT NULL,
    [Date] datetime  NOT NULL,
    [Chamber] nvarchar(max)  NOT NULL,
    [Bill_Id] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'ScheduleOfAmendments'
CREATE TABLE [dbo].[ScheduleOfAmendments] (
    [Id] nvarchar(100)  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [DocumentWordLink] nvarchar(max)  NOT NULL,
    [DocumentPDFLink] nvarchar(max)  NOT NULL,
    [DocumentHTMLLink] nvarchar(max)  NOT NULL,
    [Date] datetime  NOT NULL,
    [Bill_Id] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'ExplanatoryMemoranda'
CREATE TABLE [dbo].[ExplanatoryMemoranda] (
    [Id] nvarchar(100)  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [DocumentWordLink] nvarchar(max)  NOT NULL,
    [DocumentPDFLink] nvarchar(max)  NOT NULL,
    [DocumentHTMLLink] nvarchar(max)  NOT NULL,
    [Date] datetime  NOT NULL,
    [Bill_Id] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'HansardFullTranscripts'
CREATE TABLE [dbo].[HansardFullTranscripts] (
    [Id] nvarchar(100)  NOT NULL,
    [TranscriptXMLLink] nvarchar(max)  NOT NULL,
    [TranscriptPDFLink] nvarchar(max)  NOT NULL,
    [TranscriptHTMLLink] nvarchar(max)  NOT NULL,
    [TOCXMLLink] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'ChamberDocuments'
CREATE TABLE [dbo].[ChamberDocuments] (
    [Id] nvarchar(100)  NOT NULL,
    [Chamber] nvarchar(max)  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [IndexedContent] nvarchar(max)  NOT NULL,
    [Date] datetime  NOT NULL,
    [DocumentType] nvarchar(max)  NOT NULL,
    [DocumentPDFLink] nvarchar(max)  NOT NULL,
    [Summary] nvarchar(max)  NOT NULL,
    [DocumentXMLLink] nvarchar(max)  NOT NULL,
    [DocumentHTMLLink] nvarchar(max)  NOT NULL,
    [ParliamentNumber] int  NOT NULL,
    [DocumentNumber] nvarchar(max)  NOT NULL,
    [SequenceNumber] int  NULL,
    [PageNumber] int  NULL,
    [TOC_Id] nvarchar(100)  NULL,
    [ChamberDocumentSearchChamberDocument_ChamberDocument_Id] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'ChamberDocumentTOCs'
CREATE TABLE [dbo].[ChamberDocumentTOCs] (
    [Id] nvarchar(100)  NOT NULL,
    [TOCXML] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'ChamberDocumentSearchResults'
CREATE TABLE [dbo].[ChamberDocumentSearchResults] (
    [Id] nvarchar(100)  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [IndexedContent] nvarchar(max)  NOT NULL,
    [Date] datetime  NOT NULL,
    [Summary] nvarchar(max)  NOT NULL,
    [DocumentType] nvarchar(max)  NOT NULL,
    [ParliamentNumber] int  NOT NULL
);
GO

-- Creating table 'TabledDocuments'
CREATE TABLE [dbo].[TabledDocuments] (
    [Id] nvarchar(100)  NOT NULL,
    [Disallowable] bit  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [IndexedContent] nvarchar(max)  NOT NULL,
    [Date] datetime  NOT NULL,
    [Summary] nvarchar(max)  NOT NULL,
    [DocumentType] nvarchar(max)  NOT NULL,
    [ParliamentNumber] int  NOT NULL,
    [ChamberDocumentSearchTabledPaper_TabledPaper_Id] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'HansardSectionTranscripts'
CREATE TABLE [dbo].[HansardSectionTranscripts] (
    [Id] nvarchar(100)  NOT NULL,
    [TranscriptXML] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Questioner'
CREATE TABLE [dbo].[Questioner] (
    [HansardsAsQuestioner_Id] nvarchar(100)  NOT NULL,
    [Questioner_Id] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'Responder'
CREATE TABLE [dbo].[Responder] (
    [HansardsAsResponder_Id] nvarchar(100)  NOT NULL,
    [Responder_Id] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'Interjector'
CREATE TABLE [dbo].[Interjector] (
    [HansardsAsInterjector_Id] nvarchar(100)  NOT NULL,
    [Interjector_Id] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'Presenter'
CREATE TABLE [dbo].[Presenter] (
    [HansardsAsPresenter_Id] nvarchar(100)  NOT NULL,
    [Presenter_Id] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'Speaker'
CREATE TABLE [dbo].[Speaker] (
    [HansardsAsSpeaker_Id] nvarchar(100)  NOT NULL,
    [Speaker_Id] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'CommitteeMembers'
CREATE TABLE [dbo].[CommitteeMembers] (
    [Committees_Id] nvarchar(100)  NOT NULL,
    [Members_Id] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'BillCommittee'
CREATE TABLE [dbo].[BillCommittee] (
    [Bills_Id] nvarchar(100)  NOT NULL,
    [Committees_Id] nvarchar(100)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Bills'
ALTER TABLE [dbo].[Bills]
ADD CONSTRAINT [PK_Bills]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'BillsDigests'
ALTER TABLE [dbo].[BillsDigests]
ADD CONSTRAINT [PK_BillsDigests]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'TariffProposals'
ALTER TABLE [dbo].[TariffProposals]
ADD CONSTRAINT [PK_TariffProposals]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'BillsLists'
ALTER TABLE [dbo].[BillsLists]
ADD CONSTRAINT [PK_BillsLists]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Committees'
ALTER TABLE [dbo].[Committees]
ADD CONSTRAINT [PK_Committees]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Inquiries'
ALTER TABLE [dbo].[Inquiries]
ADD CONSTRAINT [PK_Inquiries]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'HansardSections'
ALTER TABLE [dbo].[HansardSections]
ADD CONSTRAINT [PK_HansardSections]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Parliamentarians'
ALTER TABLE [dbo].[Parliamentarians]
ADD CONSTRAINT [PK_Parliamentarians]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'GenericSearchResults'
ALTER TABLE [dbo].[GenericSearchResults]
ADD CONSTRAINT [PK_GenericSearchResults]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Reports'
ALTER TABLE [dbo].[Reports]
ADD CONSTRAINT [PK_Reports]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'NewsItems'
ALTER TABLE [dbo].[NewsItems]
ADD CONSTRAINT [PK_NewsItems]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Photos'
ALTER TABLE [dbo].[Photos]
ADD CONSTRAINT [PK_Photos]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Publications'
ALTER TABLE [dbo].[Publications]
ADD CONSTRAINT [PK_Publications]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ProposedAmendments'
ALTER TABLE [dbo].[ProposedAmendments]
ADD CONSTRAINT [PK_ProposedAmendments]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ScheduleOfAmendments'
ALTER TABLE [dbo].[ScheduleOfAmendments]
ADD CONSTRAINT [PK_ScheduleOfAmendments]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ExplanatoryMemoranda'
ALTER TABLE [dbo].[ExplanatoryMemoranda]
ADD CONSTRAINT [PK_ExplanatoryMemoranda]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'HansardFullTranscripts'
ALTER TABLE [dbo].[HansardFullTranscripts]
ADD CONSTRAINT [PK_HansardFullTranscripts]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ChamberDocuments'
ALTER TABLE [dbo].[ChamberDocuments]
ADD CONSTRAINT [PK_ChamberDocuments]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ChamberDocumentTOCs'
ALTER TABLE [dbo].[ChamberDocumentTOCs]
ADD CONSTRAINT [PK_ChamberDocumentTOCs]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ChamberDocumentSearchResults'
ALTER TABLE [dbo].[ChamberDocumentSearchResults]
ADD CONSTRAINT [PK_ChamberDocumentSearchResults]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'TabledDocuments'
ALTER TABLE [dbo].[TabledDocuments]
ADD CONSTRAINT [PK_TabledDocuments]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'HansardSectionTranscripts'
ALTER TABLE [dbo].[HansardSectionTranscripts]
ADD CONSTRAINT [PK_HansardSectionTranscripts]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [HansardsAsQuestioner_Id], [Questioner_Id] in table 'Questioner'
ALTER TABLE [dbo].[Questioner]
ADD CONSTRAINT [PK_Questioner]
    PRIMARY KEY NONCLUSTERED ([HansardsAsQuestioner_Id], [Questioner_Id] ASC);
GO

-- Creating primary key on [HansardsAsResponder_Id], [Responder_Id] in table 'Responder'
ALTER TABLE [dbo].[Responder]
ADD CONSTRAINT [PK_Responder]
    PRIMARY KEY NONCLUSTERED ([HansardsAsResponder_Id], [Responder_Id] ASC);
GO

-- Creating primary key on [HansardsAsInterjector_Id], [Interjector_Id] in table 'Interjector'
ALTER TABLE [dbo].[Interjector]
ADD CONSTRAINT [PK_Interjector]
    PRIMARY KEY NONCLUSTERED ([HansardsAsInterjector_Id], [Interjector_Id] ASC);
GO

-- Creating primary key on [HansardsAsPresenter_Id], [Presenter_Id] in table 'Presenter'
ALTER TABLE [dbo].[Presenter]
ADD CONSTRAINT [PK_Presenter]
    PRIMARY KEY NONCLUSTERED ([HansardsAsPresenter_Id], [Presenter_Id] ASC);
GO

-- Creating primary key on [HansardsAsSpeaker_Id], [Speaker_Id] in table 'Speaker'
ALTER TABLE [dbo].[Speaker]
ADD CONSTRAINT [PK_Speaker]
    PRIMARY KEY NONCLUSTERED ([HansardsAsSpeaker_Id], [Speaker_Id] ASC);
GO

-- Creating primary key on [Committees_Id], [Members_Id] in table 'CommitteeMembers'
ALTER TABLE [dbo].[CommitteeMembers]
ADD CONSTRAINT [PK_CommitteeMembers]
    PRIMARY KEY NONCLUSTERED ([Committees_Id], [Members_Id] ASC);
GO

-- Creating primary key on [Bills_Id], [Committees_Id] in table 'BillCommittee'
ALTER TABLE [dbo].[BillCommittee]
ADD CONSTRAINT [PK_BillCommittee]
    PRIMARY KEY NONCLUSTERED ([Bills_Id], [Committees_Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Bill_Id] in table 'BillsDigests'
ALTER TABLE [dbo].[BillsDigests]
ADD CONSTRAINT [FK_BillsDigestBill]
    FOREIGN KEY ([Bill_Id])
    REFERENCES [dbo].[Bills]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_BillsDigestBill'
CREATE INDEX [IX_FK_BillsDigestBill]
ON [dbo].[BillsDigests]
    ([Bill_Id]);
GO

-- Creating foreign key on [GenericSearchResultBill_Bill_Id] in table 'Bills'
ALTER TABLE [dbo].[Bills]
ADD CONSTRAINT [FK_GenericSearchResultBill]
    FOREIGN KEY ([GenericSearchResultBill_Bill_Id])
    REFERENCES [dbo].[GenericSearchResults]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_GenericSearchResultBill'
CREATE INDEX [IX_FK_GenericSearchResultBill]
ON [dbo].[Bills]
    ([GenericSearchResultBill_Bill_Id]);
GO

-- Creating foreign key on [GenericSearchResultParliamentarian_Parliamentarian_Id] in table 'Parliamentarians'
ALTER TABLE [dbo].[Parliamentarians]
ADD CONSTRAINT [FK_GenericSearchResultParliamentarian]
    FOREIGN KEY ([GenericSearchResultParliamentarian_Parliamentarian_Id])
    REFERENCES [dbo].[GenericSearchResults]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_GenericSearchResultParliamentarian'
CREATE INDEX [IX_FK_GenericSearchResultParliamentarian]
ON [dbo].[Parliamentarians]
    ([GenericSearchResultParliamentarian_Parliamentarian_Id]);
GO

-- Creating foreign key on [Inquiry_Id] in table 'Reports'
ALTER TABLE [dbo].[Reports]
ADD CONSTRAINT [FK_ReportInquiry]
    FOREIGN KEY ([Inquiry_Id])
    REFERENCES [dbo].[Inquiries]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ReportInquiry'
CREATE INDEX [IX_FK_ReportInquiry]
ON [dbo].[Reports]
    ([Inquiry_Id]);
GO

-- Creating foreign key on [Committee_Id] in table 'Inquiries'
ALTER TABLE [dbo].[Inquiries]
ADD CONSTRAINT [FK_InquiryCommittee]
    FOREIGN KEY ([Committee_Id])
    REFERENCES [dbo].[Committees]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_InquiryCommittee'
CREATE INDEX [IX_FK_InquiryCommittee]
ON [dbo].[Inquiries]
    ([Committee_Id]);
GO

-- Creating foreign key on [HansardGenericSearchResult_Hansard_Id] in table 'HansardSections'
ALTER TABLE [dbo].[HansardSections]
ADD CONSTRAINT [FK_HansardGenericSearchResult]
    FOREIGN KEY ([HansardGenericSearchResult_Hansard_Id])
    REFERENCES [dbo].[GenericSearchResults]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_HansardGenericSearchResult'
CREATE INDEX [IX_FK_HansardGenericSearchResult]
ON [dbo].[HansardSections]
    ([HansardGenericSearchResult_Hansard_Id]);
GO

-- Creating foreign key on [CommitteeGenericSearchResult_Committee_Id] in table 'Committees'
ALTER TABLE [dbo].[Committees]
ADD CONSTRAINT [FK_CommitteeGenericSearchResult]
    FOREIGN KEY ([CommitteeGenericSearchResult_Committee_Id])
    REFERENCES [dbo].[GenericSearchResults]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CommitteeGenericSearchResult'
CREATE INDEX [IX_FK_CommitteeGenericSearchResult]
ON [dbo].[Committees]
    ([CommitteeGenericSearchResult_Committee_Id]);
GO

-- Creating foreign key on [Inquiry_Id] in table 'HansardSections'
ALTER TABLE [dbo].[HansardSections]
ADD CONSTRAINT [FK_HansardInquiry]
    FOREIGN KEY ([Inquiry_Id])
    REFERENCES [dbo].[Inquiries]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_HansardInquiry'
CREATE INDEX [IX_FK_HansardInquiry]
ON [dbo].[HansardSections]
    ([Inquiry_Id]);
GO

-- Creating foreign key on [HansardsAsQuestioner_Id] in table 'Questioner'
ALTER TABLE [dbo].[Questioner]
ADD CONSTRAINT [FK_Questioner_Hansard]
    FOREIGN KEY ([HansardsAsQuestioner_Id])
    REFERENCES [dbo].[HansardSections]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Questioner_Id] in table 'Questioner'
ALTER TABLE [dbo].[Questioner]
ADD CONSTRAINT [FK_Questioner_Parliamentarian]
    FOREIGN KEY ([Questioner_Id])
    REFERENCES [dbo].[Parliamentarians]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Questioner_Parliamentarian'
CREATE INDEX [IX_FK_Questioner_Parliamentarian]
ON [dbo].[Questioner]
    ([Questioner_Id]);
GO

-- Creating foreign key on [HansardsAsResponder_Id] in table 'Responder'
ALTER TABLE [dbo].[Responder]
ADD CONSTRAINT [FK_Responder_Hansard]
    FOREIGN KEY ([HansardsAsResponder_Id])
    REFERENCES [dbo].[HansardSections]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Responder_Id] in table 'Responder'
ALTER TABLE [dbo].[Responder]
ADD CONSTRAINT [FK_Responder_Parliamentarian]
    FOREIGN KEY ([Responder_Id])
    REFERENCES [dbo].[Parliamentarians]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Responder_Parliamentarian'
CREATE INDEX [IX_FK_Responder_Parliamentarian]
ON [dbo].[Responder]
    ([Responder_Id]);
GO

-- Creating foreign key on [HansardsAsInterjector_Id] in table 'Interjector'
ALTER TABLE [dbo].[Interjector]
ADD CONSTRAINT [FK_Interjector_Hansard]
    FOREIGN KEY ([HansardsAsInterjector_Id])
    REFERENCES [dbo].[HansardSections]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Interjector_Id] in table 'Interjector'
ALTER TABLE [dbo].[Interjector]
ADD CONSTRAINT [FK_Interjector_Parliamentarian]
    FOREIGN KEY ([Interjector_Id])
    REFERENCES [dbo].[Parliamentarians]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Interjector_Parliamentarian'
CREATE INDEX [IX_FK_Interjector_Parliamentarian]
ON [dbo].[Interjector]
    ([Interjector_Id]);
GO

-- Creating foreign key on [HansardsAsPresenter_Id] in table 'Presenter'
ALTER TABLE [dbo].[Presenter]
ADD CONSTRAINT [FK_Presenter_Hansard]
    FOREIGN KEY ([HansardsAsPresenter_Id])
    REFERENCES [dbo].[HansardSections]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Presenter_Id] in table 'Presenter'
ALTER TABLE [dbo].[Presenter]
ADD CONSTRAINT [FK_Presenter_Parliamentarian]
    FOREIGN KEY ([Presenter_Id])
    REFERENCES [dbo].[Parliamentarians]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Presenter_Parliamentarian'
CREATE INDEX [IX_FK_Presenter_Parliamentarian]
ON [dbo].[Presenter]
    ([Presenter_Id]);
GO

-- Creating foreign key on [HansardsAsSpeaker_Id] in table 'Speaker'
ALTER TABLE [dbo].[Speaker]
ADD CONSTRAINT [FK_Speaker_Hansard]
    FOREIGN KEY ([HansardsAsSpeaker_Id])
    REFERENCES [dbo].[HansardSections]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Speaker_Id] in table 'Speaker'
ALTER TABLE [dbo].[Speaker]
ADD CONSTRAINT [FK_Speaker_Parliamentarian]
    FOREIGN KEY ([Speaker_Id])
    REFERENCES [dbo].[Parliamentarians]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Speaker_Parliamentarian'
CREATE INDEX [IX_FK_Speaker_Parliamentarian]
ON [dbo].[Speaker]
    ([Speaker_Id]);
GO

-- Creating foreign key on [Committee_Id] in table 'NewsItems'
ALTER TABLE [dbo].[NewsItems]
ADD CONSTRAINT [FK_NewsItemCommittee]
    FOREIGN KEY ([Committee_Id])
    REFERENCES [dbo].[Committees]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_NewsItemCommittee'
CREATE INDEX [IX_FK_NewsItemCommittee]
ON [dbo].[NewsItems]
    ([Committee_Id]);
GO

-- Creating foreign key on [NewsItemGenericSearchResult_NewsItem_Id] in table 'NewsItems'
ALTER TABLE [dbo].[NewsItems]
ADD CONSTRAINT [FK_NewsItemGenericSearchResult]
    FOREIGN KEY ([NewsItemGenericSearchResult_NewsItem_Id])
    REFERENCES [dbo].[GenericSearchResults]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_NewsItemGenericSearchResult'
CREATE INDEX [IX_FK_NewsItemGenericSearchResult]
ON [dbo].[NewsItems]
    ([NewsItemGenericSearchResult_NewsItem_Id]);
GO

-- Creating foreign key on [InquiryGenericSearchResult_Inquiry_Id] in table 'Inquiries'
ALTER TABLE [dbo].[Inquiries]
ADD CONSTRAINT [FK_InquiryGenericSearchResult]
    FOREIGN KEY ([InquiryGenericSearchResult_Inquiry_Id])
    REFERENCES [dbo].[GenericSearchResults]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_InquiryGenericSearchResult'
CREATE INDEX [IX_FK_InquiryGenericSearchResult]
ON [dbo].[Inquiries]
    ([InquiryGenericSearchResult_Inquiry_Id]);
GO

-- Creating foreign key on [GenericSearchResultPublication_Publication_Id] in table 'Publications'
ALTER TABLE [dbo].[Publications]
ADD CONSTRAINT [FK_GenericSearchResultPublication]
    FOREIGN KEY ([GenericSearchResultPublication_Publication_Id])
    REFERENCES [dbo].[GenericSearchResults]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_GenericSearchResultPublication'
CREATE INDEX [IX_FK_GenericSearchResultPublication]
ON [dbo].[Publications]
    ([GenericSearchResultPublication_Publication_Id]);
GO

-- Creating foreign key on [GenericSearchResultPhoto_Photo_Id] in table 'Photos'
ALTER TABLE [dbo].[Photos]
ADD CONSTRAINT [FK_GenericSearchResultPhoto]
    FOREIGN KEY ([GenericSearchResultPhoto_Photo_Id])
    REFERENCES [dbo].[GenericSearchResults]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_GenericSearchResultPhoto'
CREATE INDEX [IX_FK_GenericSearchResultPhoto]
ON [dbo].[Photos]
    ([GenericSearchResultPhoto_Photo_Id]);
GO

-- Creating foreign key on [Committees_Id] in table 'CommitteeMembers'
ALTER TABLE [dbo].[CommitteeMembers]
ADD CONSTRAINT [FK_CommitteeMembers_Committee]
    FOREIGN KEY ([Committees_Id])
    REFERENCES [dbo].[Committees]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Members_Id] in table 'CommitteeMembers'
ALTER TABLE [dbo].[CommitteeMembers]
ADD CONSTRAINT [FK_CommitteeMembers_Parliamentarian]
    FOREIGN KEY ([Members_Id])
    REFERENCES [dbo].[Parliamentarians]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CommitteeMembers_Parliamentarian'
CREATE INDEX [IX_FK_CommitteeMembers_Parliamentarian]
ON [dbo].[CommitteeMembers]
    ([Members_Id]);
GO

-- Creating foreign key on [ParliamentarianFirstSpeech_HansardSection_Id] in table 'HansardSections'
ALTER TABLE [dbo].[HansardSections]
ADD CONSTRAINT [FK_ParliamentarianFirstSpeech]
    FOREIGN KEY ([ParliamentarianFirstSpeech_HansardSection_Id])
    REFERENCES [dbo].[Parliamentarians]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ParliamentarianFirstSpeech'
CREATE INDEX [IX_FK_ParliamentarianFirstSpeech]
ON [dbo].[HansardSections]
    ([ParliamentarianFirstSpeech_HansardSection_Id]);
GO

-- Creating foreign key on [Bill_Id] in table 'HansardSections'
ALTER TABLE [dbo].[HansardSections]
ADD CONSTRAINT [FK_BillHansard]
    FOREIGN KEY ([Bill_Id])
    REFERENCES [dbo].[Bills]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_BillHansard'
CREATE INDEX [IX_FK_BillHansard]
ON [dbo].[HansardSections]
    ([Bill_Id]);
GO

-- Creating foreign key on [BillSecondReadingSpeeches_HansardSection_Id] in table 'HansardSections'
ALTER TABLE [dbo].[HansardSections]
ADD CONSTRAINT [FK_BillSecondReadingSpeeches]
    FOREIGN KEY ([BillSecondReadingSpeeches_HansardSection_Id])
    REFERENCES [dbo].[Bills]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_BillSecondReadingSpeeches'
CREATE INDEX [IX_FK_BillSecondReadingSpeeches]
ON [dbo].[HansardSections]
    ([BillSecondReadingSpeeches_HansardSection_Id]);
GO

-- Creating foreign key on [BillMinisterOrSponsorSpeech_HansardSection_Id] in table 'HansardSections'
ALTER TABLE [dbo].[HansardSections]
ADD CONSTRAINT [FK_BillMinisterOrSponsorSpeech]
    FOREIGN KEY ([BillMinisterOrSponsorSpeech_HansardSection_Id])
    REFERENCES [dbo].[Bills]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_BillMinisterOrSponsorSpeech'
CREATE INDEX [IX_FK_BillMinisterOrSponsorSpeech]
ON [dbo].[HansardSections]
    ([BillMinisterOrSponsorSpeech_HansardSection_Id]);
GO

-- Creating foreign key on [BillSpeechesOnConsideration_HansardSection_Id] in table 'HansardSections'
ALTER TABLE [dbo].[HansardSections]
ADD CONSTRAINT [FK_BillSpeechesOnConsideration]
    FOREIGN KEY ([BillSpeechesOnConsideration_HansardSection_Id])
    REFERENCES [dbo].[Bills]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_BillSpeechesOnConsideration'
CREATE INDEX [IX_FK_BillSpeechesOnConsideration]
ON [dbo].[HansardSections]
    ([BillSpeechesOnConsideration_HansardSection_Id]);
GO

-- Creating foreign key on [Bill_Id] in table 'ProposedAmendments'
ALTER TABLE [dbo].[ProposedAmendments]
ADD CONSTRAINT [FK_BillProposedAmendment]
    FOREIGN KEY ([Bill_Id])
    REFERENCES [dbo].[Bills]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_BillProposedAmendment'
CREATE INDEX [IX_FK_BillProposedAmendment]
ON [dbo].[ProposedAmendments]
    ([Bill_Id]);
GO

-- Creating foreign key on [Bill_Id] in table 'ScheduleOfAmendments'
ALTER TABLE [dbo].[ScheduleOfAmendments]
ADD CONSTRAINT [FK_BillScheduleOfAmendments]
    FOREIGN KEY ([Bill_Id])
    REFERENCES [dbo].[Bills]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_BillScheduleOfAmendments'
CREATE INDEX [IX_FK_BillScheduleOfAmendments]
ON [dbo].[ScheduleOfAmendments]
    ([Bill_Id]);
GO

-- Creating foreign key on [Bill_Id] in table 'Inquiries'
ALTER TABLE [dbo].[Inquiries]
ADD CONSTRAINT [FK_BillInquiry]
    FOREIGN KEY ([Bill_Id])
    REFERENCES [dbo].[Bills]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_BillInquiry'
CREATE INDEX [IX_FK_BillInquiry]
ON [dbo].[Inquiries]
    ([Bill_Id]);
GO

-- Creating foreign key on [Bills_Id] in table 'BillCommittee'
ALTER TABLE [dbo].[BillCommittee]
ADD CONSTRAINT [FK_BillCommittee_Bill]
    FOREIGN KEY ([Bills_Id])
    REFERENCES [dbo].[Bills]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Committees_Id] in table 'BillCommittee'
ALTER TABLE [dbo].[BillCommittee]
ADD CONSTRAINT [FK_BillCommittee_Committee]
    FOREIGN KEY ([Committees_Id])
    REFERENCES [dbo].[Committees]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_BillCommittee_Committee'
CREATE INDEX [IX_FK_BillCommittee_Committee]
ON [dbo].[BillCommittee]
    ([Committees_Id]);
GO

-- Creating foreign key on [Bill_Id] in table 'ExplanatoryMemoranda'
ALTER TABLE [dbo].[ExplanatoryMemoranda]
ADD CONSTRAINT [FK_BillExplanatoryMemorandum]
    FOREIGN KEY ([Bill_Id])
    REFERENCES [dbo].[Bills]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_BillExplanatoryMemorandum'
CREATE INDEX [IX_FK_BillExplanatoryMemorandum]
ON [dbo].[ExplanatoryMemoranda]
    ([Bill_Id]);
GO

-- Creating foreign key on [FullTranscript_Id] in table 'HansardSections'
ALTER TABLE [dbo].[HansardSections]
ADD CONSTRAINT [FK_HansardSectionFullTranscript]
    FOREIGN KEY ([FullTranscript_Id])
    REFERENCES [dbo].[HansardFullTranscripts]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_HansardSectionFullTranscript'
CREATE INDEX [IX_FK_HansardSectionFullTranscript]
ON [dbo].[HansardSections]
    ([FullTranscript_Id]);
GO

-- Creating foreign key on [TOC_Id] in table 'ChamberDocuments'
ALTER TABLE [dbo].[ChamberDocuments]
ADD CONSTRAINT [FK_ChamberDocumentFullTOC]
    FOREIGN KEY ([TOC_Id])
    REFERENCES [dbo].[ChamberDocumentTOCs]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ChamberDocumentFullTOC'
CREATE INDEX [IX_FK_ChamberDocumentFullTOC]
ON [dbo].[ChamberDocuments]
    ([TOC_Id]);
GO

-- Creating foreign key on [ChamberDocumentSearchTabledPaper_TabledPaper_Id] in table 'TabledDocuments'
ALTER TABLE [dbo].[TabledDocuments]
ADD CONSTRAINT [FK_ChamberDocumentSearchTabledPaper]
    FOREIGN KEY ([ChamberDocumentSearchTabledPaper_TabledPaper_Id])
    REFERENCES [dbo].[ChamberDocumentSearchResults]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ChamberDocumentSearchTabledPaper'
CREATE INDEX [IX_FK_ChamberDocumentSearchTabledPaper]
ON [dbo].[TabledDocuments]
    ([ChamberDocumentSearchTabledPaper_TabledPaper_Id]);
GO

-- Creating foreign key on [ChamberDocumentSearchChamberDocument_ChamberDocument_Id] in table 'ChamberDocuments'
ALTER TABLE [dbo].[ChamberDocuments]
ADD CONSTRAINT [FK_ChamberDocumentSearchChamberDocument]
    FOREIGN KEY ([ChamberDocumentSearchChamberDocument_ChamberDocument_Id])
    REFERENCES [dbo].[ChamberDocumentSearchResults]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ChamberDocumentSearchChamberDocument'
CREATE INDEX [IX_FK_ChamberDocumentSearchChamberDocument]
ON [dbo].[ChamberDocuments]
    ([ChamberDocumentSearchChamberDocument_ChamberDocument_Id]);
GO

-- Creating foreign key on [SectionTranscript_Id] in table 'HansardSections'
ALTER TABLE [dbo].[HansardSections]
ADD CONSTRAINT [FK_SectionTranscript]
    FOREIGN KEY ([SectionTranscript_Id])
    REFERENCES [dbo].[HansardSectionTranscripts]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_SectionTranscript'
CREATE INDEX [IX_FK_SectionTranscript]
ON [dbo].[HansardSections]
    ([SectionTranscript_Id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------